﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Configuration;
using System.Data.OleDb;
using System.Data.OracleClient;
namespace DavidDB.ADO
{
    /// <summary>
    /// 使用MYSQL需要安装 MySQLDriverCS-n-EasyQueryTools-4.0.0-DotNet2.0
    /// 下载地址 http://mysql.isu.edu.tw/Downloads/Connector-Net/mysql-connector-net-5.0.9.exe
    /// 
    /// 使用Oracle需要安装Oracle客户端
    /// </summary>
    public class DBManager : IDisposable
    {
        private OleDbConnection accessconnection = null;
        private System.Data.OleDb.OleDbTransaction accessTrans = null;

        private System.Data.SqlClient.SqlConnection sqlconnection = null;
        private System.Data.SqlClient.SqlTransaction sqlTrans = null;

        //private MySql.Data.MySqlClient.MySqlConnection mysqlconnection = null;
        //private MySql.Data.MySqlClient.MySqlTransaction mysqlTrans = null;

        private System.Data.OracleClient.OracleConnection oracleconnection = null;
        private System.Data.OracleClient.OracleTransaction oracleTrans = null;

        private System.Data.SQLite.SQLiteConnection sqliteconnection = null;
        private System.Data.SQLite.SQLiteTransaction sqliteTrans = null;

        /// <summary>
        /// 连接状态 0正常 -1异常 1已提交 2已关闭
        /// </summary>
        private PageConnectionState status = PageConnectionState.Regular;
        //   private System.Data.OracleClient.OracleConnection oracleconneciton = null;

        public DBManager()
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["connectionString"];
            if (settings != null)
            {
                string DBType = settings.ProviderName.ToLower();
                switch (DBType)
                {
                    case "system.data.sqlclient":
                        if (sqlconnection == null)
                        {
                            sqlconnection = new System.Data.SqlClient.SqlConnection(ADO.Tools.DbHelperSQL.connectionString);
                            sqlconnection.Open();
                            sqlTrans = sqlconnection.BeginTransaction();
                        }
                        break;
                    //case "mysql.data.sqlclient":
                    //    if (mysqlconnection == null)
                    //    {
                    //        mysqlconnection = new MySql.Data.MySqlClient.MySqlConnection(ADO.Tools.DbHelperSQL.connectionString);
                    //        mysqlconnection.Open();
                    //        mysqlTrans = mysqlconnection.BeginTransaction();
                    //    }
                    //    break;
                    case "system.data.oledb":
                        if (accessconnection == null)
                        {
                            accessconnection = new OleDbConnection(ADO.Tools.AccessHelper.connectionString);
                            accessconnection.Open();
                            accessTrans = accessconnection.BeginTransaction();
                        }
                        break;
                    case "system.data.oracleclient":
                        if (oracleconnection == null)
                        {
                            oracleconnection = new OracleConnection(ADO.Tools.OracleHelper.connectionString);
                            oracleconnection.Open();
                            oracleTrans = oracleconnection.BeginTransaction();
                        }
                        break;
                    case "system.data.sqlite":
                        if (sqliteconnection == null)
                        {
                            sqliteconnection = new System.Data.SQLite.SQLiteConnection(ADO.Tools.SqliteHelper.connectionString);
                            sqliteconnection.Open();
                            sqliteTrans = sqliteconnection.BeginTransaction();
                        }
                        break;
                }
            }
        }


        public DBManager(dynamic conn)
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["connectionString"];
            if (settings != null)
            {
                string DBType = settings.ProviderName.ToLower();
                switch (DBType)
                {
                    case "system.data.sqlclient":
                        sqlconnection = conn as System.Data.SqlClient.SqlConnection;
                        sqlTrans = sqlconnection.BeginTransaction();
                        break;
                    //case "mysql.data.sqlclient":
                    //    mysqlconnection = conn as MySql.Data.MySqlClient.MySqlConnection;
                    //    mysqlTrans = mysqlconnection.BeginTransaction();
                    //    break;
                    case "system.data.oledb":
                        accessconnection = conn as System.Data.OleDb.OleDbConnection;
                        accessTrans = accessconnection.BeginTransaction();
                        break;
                    case "system.data.oracleclient":
                        oracleconnection = conn as System.Data.OracleClient.OracleConnection;
                        oracleTrans = oracleconnection.BeginTransaction();
                        break;
                    case "system.data.sqlite":
                        sqliteconnection = conn as System.Data.SQLite.SQLiteConnection;
                        sqliteTrans = sqliteconnection.BeginTransaction();
                        break;
                }
            }
        }

        private DbType GetDBType()
        {
            DbType DB = new DbType();
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["connectionString"];
            if (settings != null)
            {
                string DBType = settings.ProviderName.ToLower();
                switch (DBType)
                {
                    case "system.data.sqlclient":
                        DB = DbType.MSSql;
                        break;
                    case "mysql.data.sqlclient":
                        DB = DbType.MySql;
                        break;
                    case "system.data.oledb":
                        DB = DbType.Access;
                        break;
                    case "system.data.oracleclient":
                        DB = DbType.Oracle;
                        break;
                    case "system.data.sqlite":
                        DB = DbType.Sqlite;
                        break;
                }
            }
            return DB;
        }

        public void Commit()
        {
            if (status == PageConnectionState.Regular)
            {
                DbType type = GetDBType();
                switch (type)
                {
                    case DbType.MSSql:
                        sqlTrans.Commit();
                        break;
                    //case DbType.MySql:
                    //    mysqlTrans.Commit();
                    //    break;
                    case DbType.Access:
                        accessTrans.Commit();
                        break;
                    case DbType.Oracle:
                        oracleTrans.Commit();
                        break;
                    case DbType.Sqlite:
                        sqliteTrans.Commit();
                        break;
                }
                status = PageConnectionState.Submitd;
            }
        }

        public void RollBack()
        {
            if (status == PageConnectionState.Regular && status != PageConnectionState.Submitd)
            {
                DbType type = GetDBType();
                switch (type)
                {
                    case DbType.MSSql:
                        sqlTrans.Rollback();
                        break;
                    //case DbType.MySql:
                    //    mysqlTrans.Rollback();
                    //    break;
                    case DbType.Access:
                        accessTrans.Rollback();
                        break;
                    case DbType.Oracle:
                        oracleTrans.Rollback();
                        break;
                    case DbType.Sqlite:
                        sqliteTrans.Rollback();
                        break;
                }
            }
        }

        public void Dispose()
        {

            DbType type = GetDBType();
            switch (type)
            {
                case DbType.MSSql:
                    if (status != PageConnectionState.Closed && sqlconnection.State == System.Data.ConnectionState.Open)
                    {
                        sqlconnection.Close();
                        sqlconnection = null;
                        status = PageConnectionState.Closed;
                    }
                    break;
                //case DbType.MySql:
                //    if (status != PageConnectionState.Closed && mysqlconnection.State == System.Data.ConnectionState.Open)
                //    {
                //        mysqlconnection.Close();
                //        mysqlconnection = null;
                //        status = PageConnectionState.Closed;
                //    }
                //    break;
                case DbType.Access:
                    if (status != PageConnectionState.Closed && accessconnection.State == System.Data.ConnectionState.Open)
                    {
                        accessconnection.Close();
                        accessconnection = null;
                        status = PageConnectionState.Closed;
                    }
                    break;
                case DbType.Oracle:
                    if (status != PageConnectionState.Closed && oracleconnection.State == System.Data.ConnectionState.Open)
                    {
                        oracleconnection.Close();
                        oracleconnection = null;
                        status = PageConnectionState.Closed;
                    }
                    break;
                case DbType.Sqlite:
                    if (status != PageConnectionState.Closed && sqliteconnection.State == System.Data.ConnectionState.Open)
                    {
                        sqliteconnection.Close();
                        sqliteconnection = null;
                        status = PageConnectionState.Closed;
                    }
                    break;
            }

        }

        /// <summary>
        /// 查询返回表里的所有记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<dynamic> GetList(dynamic obj)
        {
            DbType type = GetDBType();
            List<dynamic> objs = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    objs = sqlmanager.GetList(obj, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    objs = mysqlmanager.GetList(obj, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    objs = accessmanager.GetList(obj, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    objs = oraclemanager.GetList(obj, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    objs = sqlitemanager.GetList(obj, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
            return objs;
        }

        /// <summary>
        /// 执行简单SQL语句
        /// </summary>
        /// <param name="sql"></param>
        public void ExecuteScalar(string sql)
        {
            DbType type = GetDBType();
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    sqlmanager.ExecuteScalar(sql, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    mysqlmanager.ExecuteScalar(sql, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    accessmanager.ExecuteScalar(sql, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    oraclemanager.ExecuteScalar(sql, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    sqlitemanager.ExecuteScalar(sql, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
        }

        /// <summary>
        /// 执行SQL语句，返回DataReader，需要手动关闭
        /// </summary>
        /// <param name="sql"></param>
        public dynamic ExecuteReader(string sql)
        {


            DbType type = GetDBType();
            dynamic obj = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    obj = sqlmanager.ExecuteReader(sql, sqlconnection, sqlTrans);
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    obj = mysqlmanager.ExecuteReader(sql, mysqlconnection, mysqlTrans);
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    obj = accessmanager.ExecuteReader(sql, accessconnection, accessTrans);
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    obj = oraclemanager.ExecuteReader(sql, oracleconnection, oracleTrans);
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    obj = sqlitemanager.ExecuteReader(sql, sqliteconnection, sqliteTrans);
                    break;
            }
            return obj;
        }


        /// <summary>
        /// 执行SQL语句，返回查询字段
        /// </summary>
        /// <param name="sql"></param>
        public dynamic GetSingle(string sql)
        {
            DbType type = GetDBType();
            dynamic obj = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    obj = sqlmanager.GetSingle(sql, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    obj = mysqlmanager.GetSingle(sql, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    obj = accessmanager.GetSingle(sql, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    obj = oraclemanager.GetSingle(sql, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    obj = sqlitemanager.GetSingle(sql, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
            return obj;
        }

        /// <summary>
        /// 分页返回表里的所有记录
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="PageSize">每页信息量</param>
        /// <param name="NowPage">当前页</param>
        /// <returns></returns>
        public List<dynamic> GetListByPage(int PageSize, int NowPage, out int PageCount, out int recordCount, dynamic obj)
        {
            DbType type = GetDBType();
            List<dynamic> objs = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    objs = sqlmanager.GetListByPage(sqlconnection, sqlTrans, PageSize, NowPage, out PageCount, out recordCount, obj);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    objs = mysqlmanager.GetListByPage(mysqlconnection, mysqlTrans, PageSize, NowPage, out PageCount, out recordCount, obj);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    objs = accessmanager.GetListByPage(accessconnection, accessTrans, PageSize, NowPage, out PageCount, out recordCount, obj);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    objs = oraclemanager.GetListByPage(PageSize, NowPage, out PageCount, out recordCount, obj, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    objs = sqlitemanager.GetListByPage(sqliteconnection, sqliteTrans, PageSize, NowPage, out PageCount, out recordCount, obj);
                    sqlitemanager = null;
                    break;
                default:
                    PageCount = 0;
                    recordCount = 0;
                    break;
            }
            return objs;
        }

        /// <summary>
        /// 按条件分页返回表里的所有记录
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="PageSize">每页信息量</param>
        /// <param name="NowPage">当前页</param>
        /// <returns></returns>
        public List<dynamic> GetListByPageAndKeys(int PageSize, int NowPage, dynamic obj, out int PageCount, out int recordCount)
        {
            DbType type = GetDBType();
            List<dynamic> objs = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    objs = sqlmanager.GetListByPageAndKeys(sqlconnection, sqlTrans, PageSize, NowPage, obj, out PageCount, out recordCount);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    objs = mysqlmanager.GetListByPageAndKeys(mysqlconnection, mysqlTrans, PageSize, NowPage, obj, out PageCount, out recordCount);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    objs = accessmanager.GetListByPageAndKeys(accessconnection, accessTrans, PageSize, NowPage, obj, out PageCount, out recordCount);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    objs = oraclemanager.GetListByPageAndKeys(PageSize, NowPage, obj, out PageCount, out recordCount, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    objs = sqlitemanager.GetListByPageAndKeys(sqliteconnection, sqliteTrans, PageSize, NowPage, obj, out PageCount, out recordCount);
                    sqlitemanager = null;
                    break;
                default:
                    PageCount = 0;
                    recordCount = 0;
                    break;
            }
            return objs;
        }

        /// <summary>
        /// 查询按某条件查询的所有记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<dynamic> GetAllByKey(string key, string value, dynamic obj)
        {
            DbType type = GetDBType();
            List<dynamic> objs = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    objs = sqlmanager.GetAllByKey(key, value, obj, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    objs = mysqlmanager.GetAllByKey(key, value, obj, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    objs = accessmanager.GetAllByKey(key, value, obj, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    objs = oraclemanager.GetAllByKey(key, value, obj, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    objs = sqlitemanager.GetAllByKey(key, value, obj, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
            return objs;
        }

        /// <summary>
        /// 查询满足条件的记录是否唯一存在
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool IsExist(dynamic obj)
        {
            DbType type = GetDBType();
            bool isExist = false;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    isExist = sqlmanager.IsExist(obj, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    isExist = mysqlmanager.IsExist(obj, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    isExist = accessmanager.IsExist(obj, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    isExist = oraclemanager.IsExist(obj, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    isExist = sqlitemanager.IsExist(obj, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
            return isExist;
        }

        /// <summary>
        /// 查询所有满足某些条件的数据
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public List<dynamic> GetSomeObject(dynamic obj)
        {
            DbType type = GetDBType();
            List<dynamic> objs = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    objs = sqlmanager.GetSomeObject(obj, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    objs = mysqlmanager.GetSomeObject(obj, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    objs = accessmanager.GetSomeObject(obj, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    objs = oraclemanager.GetSomeObject(obj, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    objs = sqlitemanager.GetSomeObject(obj, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
            return objs;
        }


        /// <summary>
        /// 查询按某些条件查询的头几条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<dynamic> GetTopList(int count, dynamic obj)
        {
            DbType type = GetDBType();
            List<dynamic> objs = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    objs = sqlmanager.GetTopList(count, obj, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    objs = mysqlmanager.GetTopList(count, obj, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    objs = accessmanager.GetTopList(count, obj, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    objs = oraclemanager.GetTopList(count, obj, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    objs = sqlitemanager.GetTopList(count, obj, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
            return objs;
        }

        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public dynamic GetModel(dynamic obj)
        {
            DbType type = GetDBType();
            dynamic objs = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    objs = sqlmanager.GetModel(obj, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    objs = mysqlmanager.GetModel(obj, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    objs = accessmanager.GetModel(obj, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    objs = oraclemanager.GetModel(obj, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    objs = sqlitemanager.GetModel(obj, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
            return objs;
        }

        /// <summary>
        /// 保存oracle(ID无自动增长)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public int Insert(dynamic obj)
        {
            if (status == PageConnectionState.Regular)
            {
                DbType type = GetDBType();
                int objs = 0;
                switch (type)
                {
                    case DbType.MSSql:
                        Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                        try
                        {
                            objs = sqlmanager.Insert(obj, sqlconnection, sqlTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqlTrans.Rollback();
                        }
                        sqlmanager = null;
                        break;
                    //case DbType.MySql:
                    //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                    //    try
                    //    {
                    //        objs = mysqlmanager.Insert(obj, mysqlconnection, mysqlTrans);
                    //    }
                    //    catch (Exception)
                    //    {
                    //        status = PageConnectionState.Extremely;
                    //        mysqlTrans.Rollback();
                    //    }
                    //    mysqlmanager = null;
                    //    break;
                    case DbType.Access:
                        Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                        try
                        {
                            objs = accessmanager.Insert(obj, accessconnection, accessTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            accessTrans.Rollback();
                        }
                        accessmanager = null;
                        break;
                    case DbType.Oracle:
                        Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                        try
                        {
                            objs = oraclemanager.Insert(obj, oracleconnection, oracleTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            oracleTrans.Rollback();
                        }
                        oraclemanager = null;
                        break;
                    case DbType.Sqlite:
                        Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                        try
                        {
                            objs = sqlitemanager.Insert(obj, sqliteconnection, sqliteTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqliteTrans.Rollback();
                        }
                        sqlitemanager = null;
                        break;
                }
                return objs;
            }
            else
            {
                return (int)PageConnectionState.Extremely;
            }
        }


        /// <summary>
        /// 编辑一个对象并保存
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="nameSpace"></param>
        public void Edit(dynamic obj)
        {
            if (status == PageConnectionState.Regular)
            {
                DbType type = GetDBType();
                switch (type)
                {
                    case DbType.MSSql:
                        Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                        try
                        {
                            sqlmanager.Edit(obj, sqlconnection, sqlTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqlTrans.Rollback();
                        }
                        sqlmanager = null;
                        break;
                    //case DbType.MySql:
                    //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                    //    try
                    //    {
                    //        mysqlmanager.Edit(obj, mysqlconnection, mysqlTrans);
                    //    }
                    //    catch (Exception)
                    //    {
                    //        status = PageConnectionState.Extremely;
                    //        mysqlTrans.Rollback();
                    //    }
                    //    mysqlmanager = null;
                    //    break;
                    case DbType.Access:
                        Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                        try
                        {
                            accessmanager.Edit(obj, accessconnection, accessTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            accessTrans.Rollback();
                        }
                        accessmanager = null;
                        break;
                    case DbType.Oracle:
                        Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                        try
                        {
                            oraclemanager.Edit(obj, oracleconnection, oracleTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            oracleTrans.Rollback();
                        }
                        oraclemanager = null;
                        break;
                    case DbType.Sqlite:
                        Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                        try
                        {
                            sqlitemanager.Edit(obj, sqliteconnection, sqliteTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqliteTrans.Rollback();
                        }
                        sqlitemanager = null;
                        break;
                }
            }
        }
        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="obj">对象</param>
        public void Delete(dynamic obj)
        {
            if (status == PageConnectionState.Regular)
            {
                DbType type = GetDBType();
                switch (type)
                {
                    case DbType.MSSql:
                        Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                        try
                        {
                            sqlmanager.Delete(obj, sqlconnection, sqlTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqlTrans.Rollback();
                        }
                        sqlmanager = null;
                        break;
                    //case DbType.MySql:
                    //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                    //    try
                    //    {
                    //        mysqlmanager.Delete(obj, mysqlconnection, mysqlTrans);
                    //    }
                    //    catch (Exception)
                    //    {
                    //        status = PageConnectionState.Extremely;
                    //        mysqlTrans.Rollback();
                    //    }
                    //    mysqlmanager = null;
                    //    break;
                    case DbType.Access:
                        Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                        try
                        {
                            accessmanager.Delete(obj, accessconnection, accessTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            accessTrans.Rollback();
                        }
                        accessmanager = null;
                        break;
                    case DbType.Oracle:
                        Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                        try
                        {
                            oraclemanager.Delete(obj, oracleconnection, oracleTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            oracleTrans.Rollback();
                        }
                        oraclemanager = null;
                        break;
                    case DbType.Sqlite:
                        Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                        try
                        {
                            sqlitemanager.Delete(obj, sqliteconnection, sqliteTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqliteTrans.Rollback();
                        }
                        sqlitemanager = null;
                        break;
                }
            }
        }
        /// <summary>
        /// 删除表里的所有内容
        /// </summary>
        /// <param name="obj"></param>
        public void DeleteAll(dynamic obj)
        {
            if (status == PageConnectionState.Regular)
            {
                DbType type = GetDBType();
                switch (type)
                {
                    case DbType.MSSql:
                        Reflection.DAL.Managers.SQLManager manager = new Reflection.DAL.Managers.SQLManager();
                        try
                        {
                            manager.DeleteAll(obj, sqlconnection, sqlTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqlTrans.Rollback();
                        }
                        manager = null;
                        break;
                    //case DbType.MySql:
                    //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                    //    try
                    //    {
                    //        mysqlmanager.DeleteAll(obj, mysqlconnection, mysqlTrans);
                    //    }
                    //    catch (Exception)
                    //    {
                    //        status = PageConnectionState.Extremely;
                    //        mysqlTrans.Rollback();
                    //    }
                    //    mysqlmanager = null;
                    //    break;
                    case DbType.Access:
                        Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                        try
                        {
                            accessmanager.DeleteAll(obj, accessconnection, accessTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            accessTrans.Rollback();
                        }
                        accessmanager = null;
                        break;
                    case DbType.Oracle:
                        Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                        try
                        {
                            oraclemanager.DeleteAll(obj, oracleconnection, oracleTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            oracleTrans.Rollback();
                        }
                        oraclemanager = null;
                        break;
                    case DbType.Sqlite:
                        Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                        try
                        {
                            sqlitemanager.DeleteAll(obj, sqliteconnection, sqliteTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqliteTrans.Rollback();
                        }
                        sqlitemanager = null;
                        break;
                }
            }
        }

        /// <summary>
        /// 按照数据的某个字段的值删除记录
        /// </summary>
        /// <param name="obj">对象</param>
        public void DeleteColumByKey_Value(dynamic obj)
        {
            if (status == PageConnectionState.Regular)
            {
                DbType type = GetDBType();
                switch (type)
                {
                    case DbType.MSSql:
                        Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                        try
                        {
                            sqlmanager.DeleteColumByKey_Value(obj, sqlconnection, sqlTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqlTrans.Rollback();
                        }
                        sqlmanager = null;
                        break;
                    //case DbType.MySql:
                    //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                    //    try
                    //    {
                    //        mysqlmanager.DeleteColumByKey_Value(obj, mysqlconnection, mysqlTrans);
                    //    }
                    //    catch (Exception)
                    //    {
                    //        status = PageConnectionState.Extremely;
                    //        mysqlTrans.Rollback();
                    //    }
                    //    mysqlmanager = null;
                    //    break;
                    case DbType.Access:
                        Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                        try
                        {
                            accessmanager.DeleteColumByKey_Value(obj, accessconnection, accessTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            accessTrans.Rollback();
                        }
                        accessmanager = null;
                        break;
                    case DbType.Oracle:
                        Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                        try
                        {
                            oraclemanager.DeleteColumByKey_Value(obj, oracleconnection, oracleTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            oracleTrans.Rollback();
                        }
                        oraclemanager = null;
                        break;
                    case DbType.Sqlite:
                        Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                        try
                        {
                            sqlitemanager.DeleteColumByKey_Value(obj, sqliteconnection, sqliteTrans);
                        }
                        catch (Exception)
                        {
                            status = PageConnectionState.Extremely;
                            sqliteTrans.Rollback();
                        }
                        sqlitemanager = null;
                        break;
                }
            }
        }

        public List<dynamic> GetListByPageLikeKey(int PageSize, int NowPage, dynamic obj, out int PageCount, out int recordCount)
        {
            DbType type = GetDBType();
            List<dynamic> objs = null;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    objs = sqlmanager.GetListByPageLikeKey(sqlconnection, sqlTrans, PageSize, NowPage, obj, out PageCount, out recordCount);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    objs = mysqlmanager.GetListByPageLikeKey(mysqlconnection, mysqlTrans, PageSize, NowPage, obj, out PageCount, out recordCount);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    objs = accessmanager.GetListByPageLikeKey(accessconnection, accessTrans, PageSize, NowPage, obj, out PageCount, out recordCount);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    objs = oraclemanager.GetListByPageLikeKey(PageSize, NowPage, obj, out PageCount, out recordCount, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    objs = sqlitemanager.GetListByPageLikeKey(sqliteconnection, sqliteTrans, PageSize, NowPage, obj, out PageCount, out recordCount);
                    sqlitemanager = null;
                    break;
                default:
                    PageCount = 0;
                    recordCount = 0;
                    break;
            }
            return objs;
        }
        public bool IsJustExist(dynamic obj)
        {
            DbType type = GetDBType();
            bool isExist = false;
            switch (type)
            {
                case DbType.MSSql:
                    Reflection.DAL.Managers.SQLManager sqlmanager = new Reflection.DAL.Managers.SQLManager();
                    isExist = sqlmanager.IsJustExist(obj, sqlconnection, sqlTrans);
                    sqlmanager = null;
                    break;
                //case DbType.MySql:
                //    Reflection.DAL.Managers.MysqlManager mysqlmanager = new Reflection.DAL.Managers.MysqlManager();
                //    isExist = mysqlmanager.IsJustExist(obj, mysqlconnection, mysqlTrans);
                //    mysqlmanager = null;
                //    break;
                case DbType.Access:
                    Reflection.DAL.Managers.AccessManager accessmanager = new Reflection.DAL.Managers.AccessManager();
                    isExist = accessmanager.IsJustExist(obj, accessconnection, accessTrans);
                    accessmanager = null;
                    break;
                case DbType.Oracle:
                    Reflection.DAL.Managers.OracleManager oraclemanager = new Reflection.DAL.Managers.OracleManager();
                    isExist = oraclemanager.IsJustExist(obj, oracleconnection, oracleTrans);
                    oraclemanager = null;
                    break;
                case DbType.Sqlite:
                    Reflection.DAL.Managers.SqliteManager sqlitemanager = new Reflection.DAL.Managers.SqliteManager();
                    isExist = sqlitemanager.IsJustExist(obj, sqliteconnection, sqliteTrans);
                    sqlitemanager = null;
                    break;
            }
            return isExist;
        }
    }

    public enum DbType : int
    {
        MSSql = 2,
        Access = 5,
        MySql = 3,
        Oracle = 4,
        Sqlite = 6
    }

    public enum PageConnectionState : int
    {
        /// <summary>
        /// 正常 0
        /// </summary>
        Regular = 0,
        /// <summary>
        /// 异常 -1
        /// </summary>
        Extremely = -1,
        /// <summary>
        /// 已提交 1
        /// </summary>
        Submitd = 1,
        /// <summary>
        /// 已关闭 2
        /// </summary>
        Closed = 2
    }
}
